package com.softdev.system.generator.util;


import com.softdev.system.generator.entity.ClassInfo;
import com.softdev.system.generator.entity.FieldInfo;
import java.io.IOException;
import java.math.BigDecimal;
import java.time.Instant;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author xuxueli 2018-05-02 21:10:45
 * @modify zhengk/moshow 20180913
 */
public class TableParseUtil {

    private static Map<String, String> protoTypeMap = new HashMap<>(16);
    private static Map<String, String> protoTypeImportMap = new HashMap<>(16);

    static {
        initProtoTypeJavaTypeMapping();
    }
    /**
     * 解析建表SQL生成代码（model-dao-xml）
     *
     * @param tableSql
     * @return
     */
    public static ClassInfo processTableIntoClassInfo(String tableSql,String tablePrefix) throws IOException {
        if (tableSql == null || tableSql.trim().length() == 0) {
            throw new CodeGenerateException("Table structure can not be empty.");
        }
        ClassInfo classInfo = new ClassInfo();
//        tableSql = tableSql.trim().replaceAll("'", "`").replaceAll("\"", "`").replaceAll("，", ",").toLowerCase();
        tableSql = tableSql.trim().replaceAll("'", "`").replaceAll("\"", "`").replaceAll("，", ",");

        // table Name
        String tableName = null;
        if (StringUtils.containsIgnoreCase(tableSql, "TABLE") && StringUtils
            .containsIgnoreCase(tableSql, "(")) {
            tableName = tableSql.substring(StringUtils.indexOfIgnoreCase(tableSql, "TABLE") + 5,
                StringUtils.indexOfIgnoreCase(tableSql, "("));
        } else if (StringUtils.containsIgnoreCase(tableSql, "table") && StringUtils
            .containsIgnoreCase(tableSql, "(")) {
            tableName = tableSql.substring(StringUtils.indexOfIgnoreCase(tableSql, "table") + 5,
                StringUtils.indexOfIgnoreCase(tableSql, "("));
        } else {
            throw new CodeGenerateException("Table structure anomaly.");
        }
        tableName = tableName.replace(tablePrefix,"");
        //新增处理create table if not exists members情况
        if (StringUtils.containsIgnoreCase(tableName, "if not exists")) {
            tableName = tableName.replaceAll("if not exists", "");
        }
        if (StringUtils.containsIgnoreCase(tableName, "IF NOT EXISTS")) {
            tableName = tableName.replaceAll("IF NOT EXISTS", "");
        }

        if (StringUtils.containsIgnoreCase(tableName, "`")) {
            tableName = tableName.substring(tableName.indexOf("`") + 1, tableName.lastIndexOf("`"));
        } else {
            //空格开头的，需要替换掉\n\t空格
            tableName = tableName.replaceAll(" ", "").replaceAll("\n", "").replaceAll("\t", "");
        }
        //优化对byeas`.`ct_bd_customerdiscount这种命名的支持
        if (StringUtils.containsIgnoreCase(tableName, "`.`")) {
            tableName = tableName.substring(tableName.indexOf("`.`") + 3);
        } else if (StringUtils.containsIgnoreCase(tableName, ".")) {
            //优化对likeu.members这种命名的支持
            tableName = tableName.substring(tableName.indexOf(".") + 1);
        }
        // class Name
        String className = StringUtils.upperCaseFirst(StringUtils.underlineToCamelCase(tableName));
        if (className.contains("_")) {
            className = className.replaceAll("_", "");
        }

        // class Comment
        String classComment = null;
        //mysql是comment=,pgsql/oracle是comment on table,
        if (StringUtils.containsIgnoreCase(tableSql, "comment=")) {
            String classCommentTmp = tableSql
                .substring(StringUtils.lastIndexOfIgnoreCase(tableSql, "comment=") + 8)
                .replaceAll("`", "").trim();
            if (StringUtils.indexOfIgnoreCase(classCommentTmp, " ") != StringUtils
                .lastIndexOfIgnoreCase(classCommentTmp, " ")) {
                classCommentTmp = classCommentTmp
                    .substring(StringUtils.indexOfIgnoreCase(classCommentTmp, " ") + 1,
                        StringUtils.lastIndexOfIgnoreCase(classCommentTmp, " "));
            }
            if (classCommentTmp != null && classCommentTmp.trim().length() > 0) {
                classComment = classCommentTmp;
            } else {
                //修复表备注为空问题
                classComment = className;
            }
        } else if (StringUtils.containsIgnoreCase(tableSql, "comment on table")) {
            //COMMENT ON TABLE CT_BAS_FEETYPE IS 'CT_BAS_FEETYPE';
            String classCommentTmp = tableSql
                .substring(StringUtils.lastIndexOfIgnoreCase(tableSql, "comment on table") + 17)
                .trim();
            //证明这是一个常规的COMMENT ON TABLE  xxx IS 'xxxx'
            if (classCommentTmp.contains("`")) {
                classCommentTmp = classCommentTmp
                    .substring(StringUtils.indexOfIgnoreCase(classCommentTmp, "`") + 1);
                classCommentTmp = classCommentTmp
                    .substring(0, StringUtils.indexOfIgnoreCase(classCommentTmp, "`"));
                classComment = classCommentTmp;
            } else {
                //非常规的没法分析
                classComment = tableName;
            }
        } else {
            //修复表备注为空问题
            classComment = tableName;
        }
        //如果备注跟;混在一起，需要替换掉
        classComment = classComment.replaceAll(";", "");
        // field List
        List<FieldInfo> fieldList = new ArrayList<FieldInfo>();

        // 正常( ) 内的一定是字段相关的定义。
        String fieldListTmp = tableSql.substring(StringUtils.indexOfIgnoreCase(tableSql, "(") + 1,
            StringUtils.lastIndexOfIgnoreCase(tableSql, ")"));

        // 匹配 comment，替换备注里的小逗号, 防止不小心被当成切割符号切割
        Matcher matcher = Pattern.compile("comment `(.*?)\\`")
            .matcher(fieldListTmp);     // "\\{(.*?)\\}"
        while (matcher.find()) {

            String commentTmp = matcher.group();
            //2018-9-27 zhengk 不替换，只处理，支持COMMENT评论里面多种注释
            //commentTmp = commentTmp.replaceAll("\\ comment `|\\`", " ");      // "\\{|\\}"

            if (commentTmp.contains(",")) {
                String commentTmpFinal = commentTmp.replaceAll(",", "，");
                fieldListTmp = fieldListTmp.replace(matcher.group(), commentTmpFinal);
            }
        }
        //2018-10-18 zhengkai 新增支持double(10, 2)等类型中有英文逗号的特殊情况
        Matcher matcher2 = Pattern.compile("\\`(.*?)\\`")
            .matcher(fieldListTmp);     // "\\{(.*?)\\}"
        while (matcher2.find()) {
            String commentTmp2 = matcher2.group();
            if (commentTmp2.contains(",")) {
                String commentTmpFinal = commentTmp2.replaceAll(",", "，").replaceAll("\\(", "（")
                    .replaceAll("\\)", "）");
                fieldListTmp = fieldListTmp.replace(matcher2.group(), commentTmpFinal);
            }
        }
        //2018-10-18 zhengkai 新增支持double(10, 2)等类型中有英文逗号的特殊情况
        Matcher matcher3 = Pattern.compile("\\((.*?)\\)")
            .matcher(fieldListTmp);     // "\\{(.*?)\\}"
        while (matcher3.find()) {
            String commentTmp3 = matcher3.group();
            if (commentTmp3.contains(",")) {
                String commentTmpFinal = commentTmp3.replaceAll(",", "，");
                fieldListTmp = fieldListTmp.replace(matcher3.group(), commentTmpFinal);
            }
        }
        String[] fieldLineList = fieldListTmp.split(",");
        Pattern intFieldLengthCompile = Pattern.compile("\\((\\s*\\d+\\s*)\\)");// "\((\s*\d+\s*)\)"

        if (fieldLineList.length > 0) {
            int i = 0;
            //i为了解决primary key关键字出现的地方，出现在前3行，一般和id有关
            for (String columnLine : fieldLineList) {
                i++;
                columnLine = columnLine.replaceAll("\n", "").replaceAll("\t", "").trim();

                // `userid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
                // 2018-9-18 zhengk 修改为contains，提升匹配率和匹配不按照规矩出牌的语句
                // 2018-11-8 zhengkai 修复tornadoorz反馈的KEY FK_permission_id (permission_id),KEY FK_role_id (role_id)情况
                // 2019-2-22 zhengkai 要在条件中使用复杂的表达式
                // 2019-4-29 zhengkai 优化对普通和特殊storage关键字的判断（感谢@AhHeadFloating的反馈 ）
                boolean specialFlag = (!StringUtils.containsIgnoreCase(columnLine, "key ")
                    && /*!StringUtils.containsIgnoreCase(columnLine, "constraint") &&*/ !StringUtils
                    .containsIgnoreCase(columnLine, "using") && !StringUtils
                    .containsIgnoreCase(columnLine, "unique")
                    && !(StringUtils.containsIgnoreCase(columnLine, "primary")
                    && StringUtils.indexOfIgnoreCase(columnLine, "storage") + 3 > StringUtils
                    .indexOfIgnoreCase(columnLine, "("))
                    && !StringUtils.containsIgnoreCase(columnLine, "pctincrease")
                    && !StringUtils.containsIgnoreCase(columnLine, "buffer_pool") && !StringUtils
                    .containsIgnoreCase(columnLine, "tablespace")
                    && !(StringUtils.containsIgnoreCase(columnLine, "primary") && i > 3));
                boolean required = false;
                int size = -1;

                if (specialFlag) {
                    //判断是否不能为空
                    if (StringUtils.containsIgnoreCase(columnLine, " not null")) {
                        required = true;
                    }
                    //如果是oracle的number(x,x)，可能出现最后分割残留的,x)，这里做排除处理
                    if (columnLine.length() < 5) {
                        continue;
                    }
                    //2018-9-16 zhengkai 支持'符号以及空格的oracle语句// userid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
                    String columnName = "";
                    columnLine = columnLine.replaceAll("`", " ").replaceAll("\"", " ")
                        .replaceAll("'", "").replaceAll("  ", " ").trim();
                    //如果遇到username varchar(65) default '' not null,这种情况，判断第一个空格是否比第一个引号前
                    columnName = columnLine
                        .substring(0, StringUtils.indexOfIgnoreCase(columnLine, " "));

                    // field Name
                    String fieldName = StringUtils
                        .lowerCaseFirst(StringUtils.underlineToCamelCase(columnName));
                    if (fieldName.contains("_")) {
                        fieldName = fieldName.replaceAll("_", "");
                    }

                    // field class
                    columnLine = columnLine
                        .substring(StringUtils.indexOfIgnoreCase(columnLine, "`") + 1).trim();
                    // int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
                    String fieldClass = Object.class.getSimpleName();
                    //2018-9-16 zhengk 补充char/clob/blob/json等类型，如果类型未知，默认为String
                    //2018-11-22 lshz0088 处理字段类型的时候，不严谨StringUtils.containsIgnoreCase(columnLine," int") 类似这种的，可在前后适当加一些空格之类的加以区分，否则当我的字段包含这些字符的时候，产生类型判断问题。
                    if (StringUtils.containsIgnoreCase(columnLine, " int") || StringUtils
                        .containsIgnoreCase(columnLine, "smallint")) {
                        fieldClass = Integer.class.getSimpleName();
                    } else if (StringUtils.containsIgnoreCase(columnLine, "tinyint")) {
                        fieldClass = Integer.class.getSimpleName();
                        Matcher intLengthMatcher = intFieldLengthCompile.matcher(columnLine);
                        if (intLengthMatcher.find()) {
                            String intLength = intLengthMatcher.group(1);
                            if ("1".equals(StringUtils.trim(intLength))) {
                                fieldClass = Boolean.class.getSimpleName();
                            }
                        }


                    } else if (StringUtils.containsIgnoreCase(columnLine, "bigint")) {
                        fieldClass = Long.class.getSimpleName();
                    }  else if (StringUtils.containsIgnoreCase(columnLine, "bit") && getFieldSize(columnLine) == 1) {
                        fieldClass = Boolean.class.getSimpleName();
                    }
                    else if (StringUtils.containsIgnoreCase(columnLine, "float")) {
                        fieldClass = Float.class.getSimpleName();
                    } else if (StringUtils.containsIgnoreCase(columnLine, "double")) {
                        fieldClass = Double.class.getSimpleName();
                    } else if (StringUtils.containsIgnoreCase(columnLine, "datetime") || StringUtils
                        .containsIgnoreCase(columnLine, "timestamp") || StringUtils
                            .containsIgnoreCase(columnLine, "DATE")) {
                        fieldClass = Instant.class.getSimpleName();
                    } else if (StringUtils.containsIgnoreCase(columnLine, "binary") || StringUtils
                        .containsIgnoreCase(columnLine, "blob")) {
                        fieldClass = "byte[]";
                    } else if (StringUtils.containsIgnoreCase(columnLine, "varchar") || StringUtils
                        .containsIgnoreCase(columnLine, " text") || StringUtils
                        .containsIgnoreCase(columnLine, "char")
                        || StringUtils.containsIgnoreCase(columnLine, "clob") || StringUtils
                        .containsIgnoreCase(columnLine, "json")) {
                        fieldClass = String.class.getSimpleName();
                        size = getFieldSize(columnLine);
                    } else if(StringUtils.containsIgnoreCase(columnLine, " _text")){
                        fieldClass = "List<String>";
                    }else if(StringUtils.containsIgnoreCase(columnLine, " _int8")){
                        fieldClass = "List<Long>";
                    }  else if (StringUtils.containsIgnoreCase(columnLine, "decimal") || StringUtils
                        .containsIgnoreCase(columnLine, " number")) {
                        //2018-11-22 lshz0088 建议对number类型增加int，long，BigDecimal的区分判断
                        //如果startKh大于等于0，则表示有设置取值范围
                        int startKh = StringUtils.indexOfIgnoreCase(columnLine, "(");
                        if (startKh >= 0) {
                            int endKh = StringUtils.indexOfIgnoreCase(columnLine, ")", startKh);
                            String[] fanwei = columnLine.substring(startKh + 1, endKh).split("，");
                            //2019-1-5 zhengk 修复@arthaschan反馈的超出范围错误
                            //System.out.println("fanwei"+ JSON.toJSONString(fanwei));
                            //                            //number(20,6) fanwei["20","6"]
                            //                            //number(0,6) fanwei["0","6"]
                            //                            //number(20,0) fanwei["20","0"]
                            //                            //number(20) fanwei["20"]
                            //如果括号里是1位或者2位且第二位为0，则进行特殊处理。只有有小数位，都设置为BigDecimal。
                            if ((fanwei.length > 1 && "0".equals(fanwei[1]))
                                || fanwei.length == 1) {
                                int length = Integer.parseInt(fanwei[0]);
                                if (fanwei.length > 1) {
                                    length = Integer.valueOf(fanwei[1]);
                                }
                                //数字范围9位及一下用Integer，大的用Long
                                if (length <= 9) {
                                    fieldClass = Integer.class.getSimpleName();
                                } else {
                                    fieldClass = Long.class.getSimpleName();
                                }
                            } else {
                                //有小数位数一律使用BigDecimal
                                fieldClass = BigDecimal.class.getSimpleName();
                            }
                        } else {
                            fieldClass = BigDecimal.class.getSimpleName();
                        }
                    } else {
                        fieldClass = String.class.getSimpleName();
                    }

                    // field comment，MySQL的一般位于field行，而pgsql和oralce多位于后面。
                    String fieldComment;
                    if (StringUtils.containsIgnoreCase(tableSql, "comment on column") && (
                        StringUtils.containsIgnoreCase(tableSql, "." + columnName + " is ")
                            || StringUtils
                            .containsIgnoreCase(tableSql, ".`" + columnName + "` is"))) {
                        //新增对pgsql/oracle的字段备注支持
                        //COMMENT ON COLUMN public.check_info.check_name IS '检查者名称';
                        //2018-11-22 lshz0088 正则表达式的点号前面应该加上两个反斜杠，否则会认为是任意字符
                        //2019-4-29 zhengkai 优化对oracle注释comment on column的支持（@liukex）
                        tableSql = tableSql
                            .replaceAll(".`" + columnName + "` is", "." + columnName + " is");
                        Matcher columnCommentMatcher = Pattern.compile("\\." + columnName + " is `")
                            .matcher(tableSql);
                        fieldComment = columnName;
                        while (columnCommentMatcher.find()) {
                            String columnCommentTmp = columnCommentMatcher.group();
                            System.out.println(columnCommentTmp);
                            fieldComment = tableSql.substring(
                                StringUtils.indexOfIgnoreCase(tableSql, columnCommentTmp)
                                    + columnCommentTmp.length()).trim();
                            fieldComment = fieldComment.substring(0, fieldComment.indexOf("`"))
                                .trim();
                        }
                    } else if (StringUtils.containsIgnoreCase(columnLine, "comment")) {
                        String commentTmp = columnLine
                            .substring(StringUtils.indexOfIgnoreCase(columnLine, "comment") + 7)
                            .trim();
                        // '用户ID',
                        if (commentTmp.contains("`")
                            || StringUtils.indexOfIgnoreCase(commentTmp, "`") != StringUtils
                            .lastIndexOfIgnoreCase(commentTmp, "`")) {
                            commentTmp = commentTmp
                                .substring(StringUtils.indexOfIgnoreCase(commentTmp, "`") + 1,
                                    StringUtils.lastIndexOfIgnoreCase(commentTmp, "`"));
                        }
                        //解决最后一句是评论，无主键且连着)的问题:album_id int(3) default '1' null comment '相册id：0 代表头像 1代表照片墙')
                        if (commentTmp.contains(")")) {
                            commentTmp = commentTmp.substring(0,
                                StringUtils.lastIndexOfIgnoreCase(commentTmp, ")") + 1);
                        }
                        fieldComment = commentTmp;
                    } else {
                        //修复comment不存在导致报错的问题
                        fieldComment = fieldName;
                    }

                    String fieldLabel = fieldComment.split(":|：")[0];

                    FieldInfo fieldInfo = new FieldInfo();
                    fieldInfo.setColumnName(columnName);
                    fieldInfo.setFieldName(fieldName);
                    fieldInfo.setFieldClass(fieldClass);
                    fieldInfo.setFieldComment(fieldComment);
                    fieldInfo.setFieldLabel(fieldLabel);
                    fieldInfo.setRequired(required);
                    fieldInfo.setSize(size);
                    fieldInfo.setExampleValue(getFieldExampleValue(columnLine));

                    generateProtoFieldType(classInfo,fieldInfo);
                    fieldList.add(fieldInfo);
                }
            }
        }

        if (fieldList.size() < 1) {
            throw new CodeGenerateException("表结构分析失败，请检查语句或者提交issue给我");
        }


        classInfo.setTableName(tableName);
        String classNameFirstLow = className.substring(0, 1).toLowerCase() + className.substring(1);
        classInfo.setClassNameFirstLow(classNameFirstLow);
        classInfo.setClassName(className);
        classInfo.setClassComment(classComment);
        classInfo.setFieldList(fieldList);

        return classInfo;
    }

    private static void initProtoTypeJavaTypeMapping() {

        protoTypeMap.put("Integer", "int32");
        protoTypeMap.put("int", "int32");

        protoTypeMap.put("Long", "int64");
        protoTypeMap.put("long", "int64");

        protoTypeMap.put("double", "double");
        protoTypeMap.put("Double", "double");

        protoTypeMap.put("Decimal", "double");
        protoTypeMap.put("float", "float");
        protoTypeMap.put("Float", "float");

        protoTypeMap.put("Boolean", "bool");
        protoTypeMap.put("String", "string");
        protoTypeMap.put("ByteString", "bytes");

        protoTypeMap.put("BigDecimal", "boss.core.proto.number.BigDecimal");
        protoTypeImportMap.put("BigDecimal", "boss/core/proto/number/big_integer.proto");

        protoTypeMap.put("BigInteger", "boss.core.proto.number.BigInteger");
        protoTypeImportMap.put("BigInteger", "boss/core/proto/number/big_integer.proto");

        protoTypeMap.put("Instant", "google.protobuf.Timestamp");
        protoTypeImportMap.put("Instant", "google/protobuf/timestamp.proto");
    }

    private static FieldInfo generateProtoFieldType(ClassInfo classInfo, FieldInfo fieldInfo ) {
        final String fieldClass = fieldInfo.getFieldClass();
        fieldInfo.setProtoFieldType(protoTypeMap.get(fieldClass));
        classInfo.addProtoFieldTypeImport(protoTypeImportMap.get(fieldClass));

        return fieldInfo;
    }


    private static int getFieldSize(String columnLine) {
        Matcher sizeMatcher = Pattern.compile("\\((\\d+)\\)").matcher(columnLine);
        if (sizeMatcher.find()) {
            return Integer.parseInt(sizeMatcher.group(1));
        } else {
            return -1;
        }
    }

    private static String getFieldExampleValue(String columnLine) {
//        Matcher sizeMatcher =  Pattern.compile("\\[(.*?)\\]").matcher(columnLine);
        Matcher sizeMatcher = Pattern.compile("\\{(.*?)\\}").matcher(columnLine);
        if (sizeMatcher.find()) {
            String value = sizeMatcher.group(1).split(",|，")[0];
            return StringUtils.trim(value);
        }

        return null;
    }

}
